package net.xju3_4.dao;
import net.xju3_4.common.utils.JdbcUtilsV1;
import net.xju3_4.common.utils.PageInfo;
import net.xju3_4.entity.Car;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CarDao {

    public static final String SQL = "SELECT id, name FROM car_type ORDER BY id";

    public void insert(Car car) {
        String sql = "INSERT INTO car (merchant_id, type_id, brand, model, license_plate, color, daily_rent, status, images) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        JdbcUtilsV1.executeUpdate(sql,car.getMerchantId(),car.getTypeId(),car.getBrand(),car.getModel(),car.getLicensePlate(),car.getColor(),car.getDailyRent(),car.getStatus(),car.getImages());
    }

    public void deleteById(int id) {
        String sql = "UPDATE car SET deleted=1 WHERE id=?";
        JdbcUtilsV1.executeUpdate(sql, id);
    }

    public void update(Car car) {
        String sql = "UPDATE car SET merchant_id=?, type_id=?, brand=?, model=?, license_plate=?, color=?, daily_rent=?, status=?, images=? WHERE id=?";
        JdbcUtilsV1.executeUpdate(sql,car.getMerchantId(),car.getTypeId(),car.getBrand(),car.getModel(),car.getLicensePlate(),car.getColor(),car.getDailyRent(),car.getStatus(),car.getImages(),car.getId());
    }


    public Car selectOne(int id) {
        String sql = "SELECT id, merchant_id AS merchantId, type_id AS typeId, brand, model, license_plate AS licensePlate, color, daily_rent AS dailyRent, status, images FROM car WHERE id=? AND deleted=0";
        return JdbcUtilsV1.executeQueryOne(JdbcUtilsV1.getConnection(), Car.class, sql, id);
    }

    public PageInfo<Car> pageList(String licensePlate, String model, Integer typeId, String brand, Integer pageNum, Integer pageSize,Integer merchantId) {
        String sql = "SELECT \n" +
                "    id, \n" +
                "    type_id AS typeId, \n" +
                "    brand, \n" +
                "    model, \n" +
                "    license_plate AS licensePlate, \n" +
                "    color, \n" +
                "    daily_rent AS dailyRent, \n" +
                "    status, \n" +
                "    images \n" +
                "FROM car \n" +
                "WHERE deleted = 0 ";
        String countSql = "SELECT COUNT(*) FROM car WHERE deleted=0";

        List<Object> params = new ArrayList<>();
        List<Object> countParams = new ArrayList<>();

        // merchantId 过滤
        if (merchantId != null) {
            sql += " AND merchant_id = ?";
            params.add(merchantId);

            countSql += " AND merchant_id = ?";
            countParams.add(merchantId);
        }


        if (licensePlate != null && !licensePlate.trim().isEmpty()) {
            sql += " AND license_plate LIKE ?";
            params.add("%" + licensePlate.trim() + "%");

            countSql += " AND license_plate LIKE ?";
            countParams.add("%" + licensePlate.trim() + "%");
        }

        if (model != null && !model.trim().isEmpty()) {
            sql += " AND model LIKE ?";
            params.add("%" + model.trim() + "%");

            countSql += " AND model LIKE ?";
            countParams.add("%" + model.trim() + "%");
        }

        if (typeId != null) {
            sql += " AND type_id = ?";
            params.add(typeId);

            countSql += " AND type_id = ?";
            countParams.add(typeId);
        }

        if (brand != null && !brand.trim().isEmpty()) {
            sql += " AND brand LIKE ?";
            params.add("%" + brand.trim() + "%");

            countSql += " AND brand LIKE ?";
            countParams.add("%" + brand.trim() + "%");
        }

        sql += " LIMIT ?,?";
        params.add((pageNum - 1) * pageSize);
        params.add(pageSize);

        List<Car> cars = JdbcUtilsV1.executeQuery(Car.class, sql, params.toArray());

        long count = JdbcUtilsV1.executeQueryOneValue(countSql, countParams.toArray());

        // 返回分页结果
        return new PageInfo<>(pageNum, pageSize, (int) count, cars);
    }

    // 车辆浏览专用方法 - 支持价格筛选和只显示可用车辆
    public PageInfo<Car> browseCarList(Integer pageNum, Integer pageSize, String brand, Integer typeId, BigDecimal minPrice, BigDecimal maxPrice) {
        String sql = "SELECT \n" +
                "    id, \n" +
                "    merchant_id AS merchantId, \n" +
                "    type_id AS typeId, \n" +
                "    brand, \n" +
                "    model, \n" +
                "    license_plate AS licensePlate, \n" +
                "    color, \n" +
                "    daily_rent AS dailyRent, \n" +
                "    status, \n" +
                "    images \n" +
                "FROM car \n" +
                "WHERE deleted = 0 AND status = 0"; // 只显示空闲状态的车辆

        List<Object> params = new ArrayList<>();

        if (brand != null && !brand.trim().isEmpty()) {
            sql += " AND brand LIKE ?";
            params.add("%" + brand.trim() + "%");
        }

        if (typeId != null) {
            sql += " AND type_id = ?";
            params.add(typeId);
        }

        if (minPrice != null) {
            sql += " AND daily_rent >= ?";
            params.add(minPrice);
        }

        if (maxPrice != null) {
            sql += " AND daily_rent <= ?";
            params.add(maxPrice);
        }

        // 按日租金升序排列
        sql += " ORDER BY daily_rent ASC";

        sql += " LIMIT ?,?";
        params.add((pageNum - 1) * pageSize);
        params.add(pageSize);

        List<Car> cars = JdbcUtilsV1.executeQuery(Car.class, sql, params.toArray());

        // 计算总数的SQL也要加上相同的筛选条件
        String countSql = "SELECT COUNT(*) FROM car WHERE deleted=0 AND status=0";
        List<Object> countParams = new ArrayList<>();

        if (brand != null && !brand.trim().isEmpty()) {
            countSql += " AND brand LIKE ?";
            countParams.add("%" + brand.trim() + "%");
        }

        if (typeId != null) {
            countSql += " AND type_id = ?";
            countParams.add(typeId);
        }

        if (minPrice != null) {
            countSql += " AND daily_rent >= ?";
            countParams.add(minPrice);
        }

        if (maxPrice != null) {
            countSql += " AND daily_rent <= ?";
            countParams.add(maxPrice);
        }

        long count = JdbcUtilsV1.executeQueryOneValue(countSql, countParams.toArray());

        PageInfo<Car> carPageInfo = new PageInfo<>(pageNum, pageSize, (int) count, cars);
        return carPageInfo;
    }

    // 获取车辆不可租日期列表（基于rental_order表）
    public List<String> getUnavailableDates(int carId) {
        String sql = "SELECT start_time, end_time FROM rental_order " +
                     "WHERE car_id = ? AND status IN (0, 2, 4) " + // 待取车、已取车、待支付状态
                     "AND (end_time >= CURDATE() OR actual_end_time IS NULL)"; // 未来的订单或未归还的订单

        java.sql.Connection conn = JdbcUtilsV1.getConnection();
        List<String> unavailableDates = new ArrayList<>();

        try (java.sql.PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, carId);

            try (java.sql.ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    java.sql.Date startDate = rs.getDate("start_time");
                    java.sql.Date endDate = rs.getDate("end_time");

                    if (startDate != null && endDate != null) {
                        // 将日期范围转换为日期字符串列表
                        java.util.Calendar cal = java.util.Calendar.getInstance();
                        cal.setTime(startDate);

                        while (!cal.getTime().after(endDate)) {
                            unavailableDates.add(new java.text.SimpleDateFormat("yyyy-MM-dd").format(cal.getTime()));
                            cal.add(java.util.Calendar.DAY_OF_MONTH, 1);
                        }
                    }
                }
            }
        } catch (java.sql.SQLException e) {
            throw new RuntimeException("查询车辆可租日期失败", e);
        } finally {
            JdbcUtilsV1.close();
        }

        return unavailableDates;
    }

    // 获取车辆类型列表
    public List<Object> getCarTypes() {

        java.sql.Connection conn = JdbcUtilsV1.getConnection();
        List<Object> carTypes = new ArrayList<>();

        try (java.sql.PreparedStatement pstmt = conn.prepareStatement(SQL);
             java.sql.ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Map<String, Object> type = new HashMap<>();
                type.put("id", rs.getInt("id"));
                type.put("name", rs.getString("name"));
                carTypes.add(type);
            }
        } catch (java.sql.SQLException e) {
            throw new RuntimeException("查询车辆类型失败", e);
        } finally {
            JdbcUtilsV1.close();
        }

        return carTypes;
    }
}
